import os, sys, copy, time, datetime
import pickle

import numpy as np
import pandas as pd
from tqdm import tqdm
import uuid

import warnings
warnings.filterwarnings('ignore')
# sys.path.append('./')

from utils.load_access_data import *
from utils.logging_config import setup_logger

logger = setup_logger('Data Mapping')

class Config:
    jar_dir = "jarfiles/UCanAccess"
    target_cols = ['channelno', 'dataid', 'iesign', 'datatype', 'writeoffflag'
            , 'writeoffdataid', 'outputdate', 'origincountrycode', 'origincountry', 'countrycodeofdelivery'
            , 'countryofdelivery', 'importername', 'importeraddress', 'importercontact', 'suppliername'
            , 'supplieraddress', 'suppliercontact', 'hscode', 'hscodedescription', 'commoditydescription'
            , 'totalcifvalue', 'totalfobvalue', 'grossweight', 'netweight', 'quantity'
            , 'quantityunit', 'teu', 'importer_forwarderagent', 'supplier_forwarderagent', 'abnormaldata'
            , 'portofloading', 'portofdestination', 'loadingcountrycode', 'loadingcountry', 'transportterm'
            , 'tradeterm', 'paymentterm', 'carrier', 'containerno', 'vesselname'
            , 'brand', 'version', 'country', 'IMPORTER_ID', 'SUPPLIER_ID']
    chunk_size=20000
config = Config
jar_dir = config.jar_dir


def ARG_get_target_import(imp_path):
    """
    阿根廷 进口
    拉丁美洲	ARGENTINA	阿根廷	ARG
    """
    start=time.time()
    # 获取表名
    conn, stmt = connect_access(imp_path, jar_dir)
    table_names = get_table_names(conn, stmt)
    # table_names

    conn, stmt = connect_access(imp_path, jar_dir)
    imp_df = load_table(table_names[0], conn, stmt, chunk_size=config.chunk_size)

    target_tab = pd.DataFrame(columns=config.target_cols)

    target_tab['channelno'] = ['zdzj']*imp_df.shape[0]
    target_tab['iesign'] = ['I']*imp_df.shape[0]
    target_tab['writeoffflag'] = ['o']*imp_df.shape[0]
    target_tab['outputdate'] = [f'{int(x[0]):04d}-{int(x[1]):02d}-{int(x[2]):02d}' for x in imp_df.loc[:,['YEAR', 'MONTH', 'DAY']].values]
    # 需要转写为标准英文国名
    target_tab['origincountrycode'] = imp_df['ORIGIN_COUNTRY']
    target_tab['origincountry'] = imp_df['ORIGIN_COUNTRY']
    target_tab['countrycodeofdelivery'] = imp_df['SALES_COUNTRY']
    target_tab['countryofdelivery'] = imp_df['SALES_COUNTRY']
    
    target_tab['importername'] = imp_df['IMPORTER']
    target_tab['hscode'] = imp_df['HS_CODE']
    target_tab['commoditydescription'] = [x.replace("\"", " ") if x is not None else x for x in imp_df['PRODUCT_DESC'].values]
    target_tab['totalcifvalue'] = imp_df['ITEM_CIF']
    target_tab['totalfobvalue'] = imp_df['QUANTITY']
    target_tab['grossweight'] = imp_df['G_WEIGHT']
    target_tab['quantity'] = imp_df['QUANTITY']
    target_tab['quantityunit'] = imp_df['UNIT_OF_QUANTITY']
    # 需要判断根据imp_df['IMPORTER']判断是否为0/1
    target_tab['importer_forwarderagent'] = [0]*imp_df.shape[0]
    
    # 待判定
    target_tab['abnormaldata'] = [0]*imp_df.shape[0]
    
    target_tab['portofloading'] = imp_df['ORIGIN_PORT']
    target_tab['portofdestination'] = imp_df['CUSTOMS']
    # 国家需转换为标准英文国名
    target_tab['loadingcountrycode'] = imp_df['ORIGIN_COUNTRY']
    target_tab['loadingcountry'] = imp_df['ORIGIN_COUNTRY']
    
    # 需映射成统一枚举值
    target_tab['transportterm'] = imp_df['TRANS_TYPE']
    
    # 需映射成统一枚举值
    target_tab['tradeterm'] = imp_df['INCOTERMS']
    target_tab['brand'] = imp_df['BRAND']
    target_tab['IMPORTER_ID'] = imp_df['IMPORT_ID']

    end=time.time()
    logger.info(f'Import data mapping and cleaning takes {round((end-start)/60,2)} minutes')

    return target_tab


def ARG_get_target_export(exp_path):
    """
    阿根廷 出口
    1 拉丁美洲	ARGENTINA	阿根廷	ARG
    """
    start=time.time()
    # 获取表名
    conn, stmt = connect_access(exp_path, jar_dir)
    table_names = get_table_names(conn, stmt)
    # table_names

    conn, stmt = connect_access(exp_path, jar_dir)
    exp_df = load_table(table_names[0], conn, stmt, chunk_size=config.chunk_size)

    target_tab = pd.DataFrame(columns=config.target_cols)

    target_tab['channelno'] = ['zdzj']*exp_df.shape[0]
    target_tab['iesign'] = ['E']*exp_df.shape[0]
    target_tab['writeoffflag'] = ['o']*exp_df.shape[0]
    target_tab['outputdate'] = [f'{int(x[0]):04d}-{int(x[1]):02d}-{int(x[2]):02d}' for x in exp_df.loc[:,['YEAR', 'MONTH', 'DAY']].values]
    # 原产来源国需要再确认
    target_tab['origincountrycode'] = ['ARG']*exp_df.shape[0]
    target_tab['origincountry'] = ['ARGENTINA']*exp_df.shape[0]
    # 需要转写为标准英文国名
    target_tab['countrycodeofdelivery'] = exp_df['DEST_COUNTRY']
    target_tab['countryofdelivery'] = exp_df['DEST_COUNTRY']
    
    target_tab['suppliername'] = exp_df['EXPORTER']
    target_tab['hscode'] = exp_df['HS_CODE']
    target_tab['commoditydescription'] = [x.replace("\"", " ") if x is not None else x for x in exp_df['PRODUCT_DESC'].values]
    target_tab['totalcifvalue'] = exp_df['ITEM_CIF']
    target_tab['totalfobvalue'] = exp_df['QUANTITY']
    target_tab['grossweight'] = exp_df['G_WEIGHT']
    target_tab['quantity'] = exp_df['QUANTITY']
    target_tab['quantityunit'] = exp_df['UNIT_OF_QUANTITY']
    # 需要判断根据imp_df['EXPORTER']判断是否为0/1
    target_tab['supplier_forwarderagent'] = [0]*exp_df.shape[0]
    
    # 待判定
    target_tab['abnormaldata'] = [0]*exp_df.shape[0]
    
    target_tab['portofloading'] = exp_df['CUSTOMS']
    # 国家需转换为标准英文国名
    target_tab['loadingcountrycode'] = ['ARG']*exp_df.shape[0]
    target_tab['loadingcountry'] = ['ARGENTINA']*exp_df.shape[0]
    
    # 需映射成统一枚举值
    target_tab['transportterm'] = exp_df['TRANS_TYPE']
    
    # 需映射成统一枚举值
    target_tab['tradeterm'] = exp_df['INCOTERMS']
    target_tab['brand'] = exp_df['BRAND']
    target_tab['SUPPLIER_ID'] = exp_df['EXPORT_ID']

    end=time.time()
    logger.info(f'Export data mapping and cleaning takes {round((end-start)/60,2)} minutes')

    return target_tab

def LSO_get_target_import(imp_path):
    """
    莱索托 进口
    17	非洲	LESOTHO	莱索托	LSO
    """
    start = time.time()
    # 获取表名
    conn, stmt = connect_access(imp_path, jar_dir)
    table_names = get_table_names(conn, stmt)
    # table_names

    conn, stmt = connect_access(imp_path, jar_dir)
    imp_df = load_table(table_names[0], conn, stmt, chunk_size=config.chunk_size)

    target_tab = pd.DataFrame(columns=config.target_cols)

    target_tab['channelno'] = ['zdzj'] * imp_df.shape[0]
    target_tab['dataid'] = [str(uuid.uuid4()).replace('-', '') for _ in range(exp_df.shape[0])]
    target_tab['iesign'] = ['I'] * imp_df.shape[0]
    target_tab['writeoffflag'] = ['o'] * imp_df.shape[0]
    target_tab['outputdate'] = [f'{int(x[0]):04d}-{int(x[1]):02d}-{int(x[2]):02d}' for x in imp_df.loc[:, ['YEAR', 'MONTH', 'DAY']].values]
    # 需要转写为标准英文国名
    target_tab['origincountrycode'] = imp_df['ORIGIN_COUNTRY']
    target_tab['origincountry'] = imp_df['ORIGIN_COUNTRY']

    target_tab['importername'] = imp_df['IMPORTER_NAME']
    target_tab['hscode'] = imp_df['HS_CODE']
    target_tab['hscodedescription'] = imp_df['HS_CODE_DESC']
    target_tab['commoditydescription'] = [x.replace("\"", " ") if x is not None else x for x in
                                          imp_df['PRODUCT_DESC'].values]

    target_tab['grossweight'] = imp_df['G_WEIGHT']
    target_tab['netweight'] = imp_df['N_WEIGHT']
    target_tab['quantity'] = imp_df['PACKAGES']
    target_tab['quantityunit'] = imp_df['UNIT_OF_MEASURE']
    # 需要判断根据imp_df['IMPORTER']判断是否为0/1
    target_tab['importer_forwarderagent'] = [0] * imp_df.shape[0]

    # 待判定
    target_tab['abnormaldata'] = [0] * imp_df.shape[0]

    target_tab['portofloading'] = imp_df['EXIT_PORT']
    target_tab['portofdestination'] = imp_df['ENTRY_PORT']
    # 国家需转换为标准英文国名
    target_tab['loadingcountrycode'] = imp_df['ORIGIN_COUNTRY']
    target_tab['loadingcountry'] = imp_df['ORIGIN_COUNTRY']

    # 需映射成统一枚举值
    target_tab['transportterm'] = imp_df['TRANS_TYPE']
    
    target_tab['version'] = 1
    
    target_tab['IMPORTER_ID'] = imp_df['IMPORTER_ID']

    end = time.time()
    logger.info(f'Import data mapping and cleaning takes {round((end - start) / 60, 2)} minutes')

    return target_tab

def LSO_get_target_export(exp_path):
    """
    莱索托 出口
    17	非洲	LESOTHO	莱索托	LSO
    """
    start = time.time()
    # 获取表名
    conn, stmt = connect_access(exp_path, jar_dir)
    table_names = get_table_names(conn, stmt)
    # table_names

    conn, stmt = connect_access(exp_path, jar_dir)
    exp_df = load_table(table_names[0], conn, stmt, chunk_size=config.chunk_size)

    target_tab = pd.DataFrame(columns=config.target_cols)

    target_tab['channelno'] = ['zdzj'] * exp_df.shape[0]
    target_tab['dataid'] = [str(uuid.uuid4()).replace('-', '') for _ in range(exp_df.shape[0])]
    target_tab['iesign'] = ['E'] * exp_df.shape[0]
    target_tab['writeoffflag'] = ['o'] * exp_df.shape[0]
    target_tab['outputdate'] = [f'{int(x[0]):04d}-{int(x[1]):02d}-{int(x[2]):02d}' for x in
                                exp_df.loc[:, ['YEAR', 'MONTH', 'DAY']].values]
    # 原产来源国需要再确认
    target_tab['origincountrycode'] = exp_df['ORIGIN_COUNTRY']
    target_tab['origincountry'] = exp_df['ORIGIN_COUNTRY']
    # 需要转写为标准英文国名
    target_tab['countrycodeofdelivery'] = exp_df['DEST_COUNTRY']
    target_tab['countryofdelivery'] = exp_df['DEST_COUNTRY']

    target_tab['suppliername'] = exp_df['EXPORTER_NAME']
    target_tab['hscode'] = exp_df['HS_CODE']
    target_tab['commoditydescription'] = [x.replace("\"", " ") if x is not None else x for x in exp_df['PRODUCT_DESC'].values]

    target_tab['grossweight'] = exp_df['G_WEIGHT']
    target_tab['netweight'] = exp_df['N_WEIGHT']
    target_tab['quantity'] = exp_df['PACKAGES']
    # target_tab['quantityunit'] = exp_df['UNIT_OF_MEASURE']
    # 需要判断根据imp_df['EXPORTER']判断是否为0/1
    target_tab['supplier_forwarderagent'] = [0] * exp_df.shape[0]

    # 待判定
    target_tab['abnormaldata'] = [0] * exp_df.shape[0]

    # target_tab['portofloading'] = exp_df['CUSTOMS']
    # 国家需转换为标准英文国名
    target_tab['loadingcountrycode'] = ['LSO'] * exp_df.shape[0]
    target_tab['loadingcountry'] = ['LESOTHO'] * exp_df.shape[0]

    target_tab['version'] = 1

    # target_tab['SUPPLIER_ID'] = exp_df['EXPORT_ID']

    end = time.time()
    logger.info(f'Export data mapping and cleaning takes {round((end - start) / 60, 2)} minutes')

    return target_tab


def get_function_map(country_code_dict):
    """
    输入: country_code_dict, Key为['洲别', '英文国名', '中文国名', '国别编码']
    """
    country_list = ['阿根廷', '莱索托']
    function_map = [x for x in country_code_dict if x.get('中文国名') in country_list]
    for x in function_map:
        if x.get('中文国名')=='阿根廷':
            x.update({'import_fuc':ARG_get_target_import, 'export_fuc':ARG_get_target_export})
        if x.get('中文国名')=='莱索托':
            x.update({'import_fuc':LSO_get_target_import, 'export_fuc':LSO_get_target_export})
    return function_map



